package proyecto1.Manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import proyecto1.GestionDB;
import proyecto1.TO.DeudaTO;

public class DeudaManager 
{
    GestionDB cnn=new GestionDB();
    
    public List<DeudaTO> listar() throws Exception
    {
        ArrayList data=new ArrayList();
        String sql="SELECT * FROM deuda";
        cnn.conectar();
        ResultSet rs=cnn.ejecutarConsulta(sql);
        while(rs.next())
        {
            DeudaTO t = new DeudaTO();
            t.setID_deuda(rs.getString("ID_deuda"));
            t.setDebito_deuda(rs.getString("Debito_deuda"));
            t.setMotivo_deuda(rs.getString("Motivo_deuda"));
            t.setID_asistencia(rs.getString("ID_asistencia"));
            t.setDetalle_deuda(rs.getString("Detalle_deuda"));
            t.setID_pago(rs.getString("ID_pago"));
            
        }
        rs.close();
        cnn.desconectar();
        return data;
    }
    
    public void Agregar(DeudaTO data) throws Exception {

        String sql = "INSERT INTO deuda(ID_deuda,Debito_deuda,Motivo_deuda,ID_asistencia,Detalle_deuda,ID_pago) VALUES (?,?,?,?,?,?)";
        Connection c = null;
        try {
            c = cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, data.getID_deuda());
            ps.setString(2, data.getDebito_deuda());
            ps.setString(3, data.getMotivo_deuda());
            ps.setString(4, data.getID_asistencia());
            ps.setString(5, data.getDetalle_deuda());
            ps.setString(6, data.getID_pago());
            ps.executeUpdate();
            c.commit();
            ps.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        } finally {
            cnn.desconectar();
        }
    }
    
    public DeudaTO buscaPorid(Object id) throws Exception {
        DeudaTO t = null;
        String sql = "SELECT * FROM deuda WHERE ID_deuda=" + id;
        //id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe
        try {
            cnn.conectar();
            ResultSet rs = cnn.ejecutarConsulta(sql);
            if (rs.next()) {
                t = new DeudaTO();
                t.setID_deuda(rs.getString("ID_deuda"));
                t.setDebito_deuda(rs.getString("Debito_deuda"));
                t.setMotivo_deuda(rs.getString("Motivo_deuda"));
                t.setID_asistencia(rs.getString("ID_asistencia"));
                t.setDetalle_deuda(rs.getString("Detalle_deuda"));
                t.setID_pago(rs.getString("ID_pago"));
            }
            rs.close();
            cnn.desconectar();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        return t;
    }
    
    public void Editar(DeudaTO data) throws Exception {
        String sql = "UPDATE deuda SET Debito_deuda=?,Motivo_deuda=?,ID_asistencia=?,Detalle_deuda=?,ID_pago=? WHERE ID_deuda=?";
        Connection c = null;
        //id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe
        try {
            c = cnn.conectar();
            c.setAutoCommit(false);
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setString(5, data.getID_deuda());
                ps.setString(1, data.getDebito_deuda());
                ps.setString(2, data.getMotivo_deuda());
                ps.setString(3, data.getID_asistencia());
                ps.setString(4, data.getDetalle_deuda());
                ps.setString(5, data.getID_pago());
                ps.executeUpdate();
                c.commit();
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        } finally {
            cnn.desconectar();
        }

    }
    
    public void eliminar(String id) throws Exception {
        try {
            Connection c = cnn.conectar();
            String sql = "DELETE FROM deuda WHERE ID_deuda=?";
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, id);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        } finally {
            cnn.desconectar();
        }

    }
    
    public List<DeudaTO> buscaPorDescripcion(Object des)throws Exception
    {
        ArrayList data = new ArrayList();
        DeudaTO t=null;
        String sql="SELECT * FROM deuda WHERE Motivo_deuda LIKE '"+des+"%'";
        try {
        cnn.conectar();
        ResultSet rs=cnn.ejecutarConsulta(sql);
        while(rs.next())
        {
            t=new DeudaTO();
            t.setID_deuda(rs.getString("ID_deuda"));
            t.setDebito_deuda(rs.getString("Debito_deuda"));
            t.setMotivo_deuda(rs.getString("Motivo_deuda"));
            t.setID_asistencia(rs.getString("ID_asistencia"));
            t.setDetalle_deuda(rs.getString("Detalle_deuda"));
            t.setID_pago(rs.getString("ID_pago"));
            data.add(t);
        }
           rs.close();
           cnn.desconectar();
           
        } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e.getMessage());
    }
   return data;
   }
}